import os
import json
import sqlite3
from tabulate import tabulate

# 定义数据库路径和表名
db_path = 'code_analysis.db'
table_name = 'code_analysis_results'

# 创建SQLite数据库连接
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# 删除现有表（如果存在）
drop_table_query = f"DROP TABLE IF EXISTS {table_name};"
cursor.execute(drop_table_query)
conn.commit()

# 创建表
create_table_query = f"""
CREATE TABLE IF NOT EXISTS {table_name} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    encryption INTEGER,
    decryption INTEGER,
    signature INTEGER,
    verification INTEGER,
    algorithm TEXT
);
"""
cursor.execute(create_table_query)
conn.commit()

# 读取output文件夹中的所有JSON文件
def read_json_files(folder_path):
    json_files = []
    for filename in os.listdir(folder_path):
        if filename.endswith(".json"):
            file_path = os.path.join(folder_path, filename)
            with open(file_path, 'r', encoding='utf-8') as file:
                json_files.append(json.load(file))
    return json_files

# 插入数据到数据库
def insert_data_to_db(json_files, cursor, conn):
    for json_file in json_files:
        # 提取字段
        encryption = json_file.get('encryption', 0)
        decryption = json_file.get('decryption', 0)
        signature = json_file.get('signature', 0)
        verification = json_file.get('verification', 0)
        algorithm = json_file.get('algorithm', "")
        
        insert_query = f"""
        INSERT INTO {table_name} (encryption, decryption, signature, verification, algorithm)
        VALUES (?, ?, ?, ?, ?);
        """
        cursor.execute(insert_query, (encryption, decryption, signature, verification, algorithm))
    
    conn.commit()

# 从数据库中读取并打印数据
def print_data_from_db(db_path, table_name):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 查询所有数据
    query = f"SELECT * FROM {table_name};"
    cursor.execute(query)
    rows = cursor.fetchall()
    
    # 提取列名
    column_names = [description[0] for description in cursor.description]
    
    # 打印表格
    print(tabulate(rows, headers=column_names, tablefmt='grid'))
    
    conn.close()

# 主函数入口
if __name__ == "__main__":
    output_folder = "output"
    json_files = read_json_files(output_folder)
    insert_data_to_db(json_files, cursor, conn)
    print(f"所有JSON文件已成功导入到 {db_path} 数据库的 {table_name} 表中")
    
    # 从数据库中读取并打印数据
    print_data_from_db(db_path, table_name)

# 关闭数据库连接
conn.close()